 
//////////////////////////////////////////////////////////////////////////////
/*
	*This file creates the following datasets
	1) 01_EMBI_Data.dta: EMBI Dataset - for Argentina & other LAC countries
	2) 01_GlobalFactors_Controls.dta: Global controls
	3) 01_Arg_ExchangeRate.dta: Argentina NXR
	4) 01_NXR_weekly.dta: Weekly NXR
	5) 01_Arg_Merval.dta: Argentina Merval (stock index) price
	6) 01_Arg_Inflation.dta: Inflation announcement dates & misreports	
	7) 01_Arg_Bonds_Data.dta: Data on Argentina's bonds
	8) 01_Arg_Bonds_StaticInfo.dta: Data on Argentina's bonds - static info
	9) 02_Arg_merged.dta: Merged dataset

	*INPUTS (raw data)
	00_EMBI_Index.xls
	00_GlobalFactors.xls
	00_Arg_Exchange_Rate.xls
	00_Arg_Merval.xls
	00_Arg_Inflation_Announcements.xls
	00_Arg_Bond_Yields.xls	
*/
//////////////////////////////////////////////////////////////////////////////


set more off
clear all
cls
local path_rawdata 	= "00_data/00_raw_data"
local path_inputs 	= "00_data/01_datasets/00_inputs"

/////////////////////////////////////////////////////////////////////////////////////
//DATASET 1: IMPORT EMBI DATASET (Argentina and other LAC countries)
////////////////////////////////////////////////////////////////////////////////////
import excel "`path_rawdata'/00_EMBI_Index.xls", sheet("EMBI") cellrange(A1:G5844) firstrow clear
format Date %d
keep if Date>=d(01jan2004)
qui gen day   = day(Date)
qui gen month = month(Date)
qui gen year  = year(Date)
order (Date day month year), first
save "`path_inputs'/01_EMBI_Data.dta",replace


///////////////////////////////////////////////////////////////////////////////////
//DATASET 2: IMPORT GLOBAL FACTORS
///////////////////////////////////////////////////////////////////////////////////
import excel "`path_rawdata'/00_GlobalFactors.xls", sheet("Factors") cellrange(A1:D3526) firstrow clear
format Date %d  
	label variable VIX_CLOSE "Close of VIX Index"
	label variable SP_CLOSE "Price Close of Sp500 Index"
	label variable EEM_CLOSE "MSCI Emerging Markets ETF (Equity)"
	drop if Date >=d(01jan2014)
save "`path_inputs'/01_GlobalFactors_Controls.dta",replace



///////////////////////////////////////////////////////////////////////////////////
//DATASET 3: Import Argentina Exchange Rate: Blue, Official, and Forwards
///////////////////////////////////////////////////////////////////////////////////
//Spot Exchange Rate
import excel "`path_rawdata'/00_Arg_Exchange_Rate.xls", sheet("Spot") cellrange(A1:C3675) firstrow clear
format Date %d  
save "`path_inputs'/01_Arg_ExchangeRate.dta",replace

// Forward Exchange Rate 12 months
import excel "`path_rawdata'/00_Arg_Exchange_Rate.xls", sheet("Forwards") cellrange(A6:B4401) firstrow clear
format Date %d 
rename PX_LAST Forward_12
save "`path_inputs'/01_Arg_ExchangeRate_tmp2.dta",replace

// Forward Exchange Rate 6 months
import excel "`path_rawdata'/00_Arg_Exchange_Rate.xls", sheet("Forwards") cellrange(G6:H4472) firstrow clear
format Date %d 
rename PX_LAST Forward_6
save "`path_inputs'/01_Arg_ExchangeRate_tmp3.dta",replace

// Forward Exchange Rate 1 month
import excel "`path_rawdata'/00_Arg_Exchange_Rate.xls", sheet("Forwards") cellrange(D6:E4593) firstrow clear
format Date %d 
rename PX_LAST Forward_1
save "`path_inputs'/01_Arg_ExchangeRate_tmp4.dta",replace

// Merge all
use "`path_inputs'/01_Arg_ExchangeRate.dta", clear
foreach i in 2 3 4 {
merge 1:1 Date using "`path_inputs'/01_Arg_ExchangeRate_tmp`i'.dta"
drop if _merge==2
drop _merge
}

// Fill in Missing Dates (with previous observation)
foreach i in 12 6 1 {
replace Forward_`i' = Forward_`i'[_n-1] if Forward_`i'[_n]==.
}
*Save file
save "`path_inputs'/01_Arg_ExchangeRate.dta",replace

*Delete tmp files
sleep 1000
foreach i in 2 3 4 {
erase "`path_inputs'/01_Arg_ExchangeRate_tmp`i'.dta"
}


*** Weekly NXR
use "`path_inputs'/01_Arg_ExchangeRate.dta", clear
qui gen week = week(Date)
qui gen year = year(Date)
collapse (mean) TC_Blue TC_Official, by(year week)
sort year week
qui gen TC_Bluem1     = TC_Blue[_n-1]
qui gen TC_Officialm1 = TC_Official[_n-1]
qui gen time=_n
tsset time
qui gen Obs12Mdep = (F52.TC_Official/TC_Official-1)*100
drop time
save "`path_inputs'/01_NXR_weekly.dta", replace



///////////////////////////////////////////////////////////////////////////////////
//DATASET 4: Argentina's MERVAL stock index
///////////////////////////////////////////////////////////////////////////////////
//Closing Price of Merval Index - Daily Data
import excel "`path_rawdata'/00_Arg_MERVAL.xls",  cellrange(A1:B3948) firstrow clear
format Date %d 
rename Close Merval_px
save "`path_inputs'/01_Arg_Merval.dta",replace



///////////////////////////////////////////////////////////////////////////////////////////////////////
//DATASET 5: ARGENTINA's INFLATION: ANNOUNCED INFLATION (INDEC) and PRIVATE MEASURES (monthly rates)
///////////////////////////////////////////////////////////////////////////////////////////////////////
import excel "`path_rawdata'/00_Arg_Inflation_Announcements.xls", sheet("DATA") cellrange(A2:H122) firstrow clear
format AnnouncementDate %d

* Compute average and STD across the private measures of inflation
qui egen Inf_Private_AVG = rowmean(IPC_FIEL PriceStats IPC7 IPC_BAC IPC_Congreso)
qui egen Inf_Private_STD = rowsd(IPC_FIEL PriceStats IPC7 IPC_BAC IPC_Congreso)
	label variable Inf_Private_AVG "Average for the Alternatives Monthly Inflation Rates"
	label variable Inf_Private_STD "Standard Dev. for the Alternatives Monthly Inflation Rates"

* LINK WITH THE QUANT MODEL (compute noise of the signal \pi)
local var_inflation = "IPC_FIEL PriceStats IPC7 IPC_BAC IPC_Congreso"
foreach var of local var_inflation {
gen diff_`var' = 3*(IPC_INDEC - `var')/100
}
qui egen Inf_Misreport_STD = rowsd(diff_IPC_FIEL diff_PriceStats diff_IPC7 diff_IPC_BAC diff_IPC_Congreso)
sum(Inf_Misreport_STD)

* Compute Observed Misreport (negative value implies underreport of inflation)
qui gen Inf_Misreport    = IPC_INDEC    - Inf_Private_AVG
label variable Inf_Misreport "Underreport of inflation (INDEC-AVG), Monthly"

* Compute Change in the observed Misreport
sort Month
gen date_id = _n
tset date_id
qui gen Inf_Misreport_Change    = (Inf_Misreport - L.Inf_Misreport)
qui drop date_id

* Compute Annualized Inflation Rate [12-month rolling window]
local var_inflation = "IPC_INDEC Inf_Private_AVG IPC_FIEL PriceStats IPC7 IPC_BAC IPC_Congreso"
foreach var of local var_inflation {
    qui gen `var'_tmp   = ln(`var'/100+1)  // Monthly Inflation
	forval i = 0/11 {
	qui gen xxR_`i' = `var'_tmp[_n-`i']
	}
	qui gen xx = (xxR_0 + xxR_1 + xxR_2 + xxR_3 + xxR_4 + xxR_5 + xxR_6 + xxR_7 + xxR_8 + xxR_9 + xxR_10 + xxR_11)
	forval i = 0/11 {
	drop xxR_`i'
	}
	qui   gen      `var'_Annualized     =  (exp(xx)-1)*100   // Annualized Rate
    drop xx `var'_tmp
	}	

qui rename IPC_INDEC_Annualized        INDEC_Annualized
qui rename Inf_Private_AVG_Annualized  Private_Annualized 
qui   gen      Misreport_Annualized =  Private_Annualized-INDEC_Annualized
label variable INDEC_Annualized        "IPC_INDEC, Annualized"
label variable Private_Annualized      "Inf_Private_AVG, Annualized"


rename Month Date_Month
order Date_Month, first
replace Date_Month = mofd(Date_Month)
format Date_Month %tm

qui gen day   = day(AnnouncementDate)
qui gen month = month(AnnouncementDate)
qui gen year  = year(AnnouncementDate)
qui gen Measured_Month = mofd(AnnouncementDate)-1 // This variable correspond to the month that inflation was measured
format Measured_Month %tm

gen tmp_date = dofm(Measured_Month)
format tmp_date %d
qui gen year_reported = year(tmp_date)
qui gen month_reported = month(tmp_date)
drop tmp_date

order Measured_Month AnnouncementDate day month year, first
rename AnnouncementDate Date
qui gen Misreport_Annualized_Change = Misreport_Annualized[_n]-Misreport_Annualized[_n-1]
save "`path_inputs'/01_Arg_Inflation.dta",replace


//////////////////////////////////////////////////////////////////////////////
//MERGE DATASETS
//////////////////////////////////////////////////////////////////////////////
use "`path_inputs'/01_EMBI_Data.dta", clear
preserve
use "`path_inputs'/01_Arg_Inflation.dta",clear
keep if Date!=.
save tmp_file.dta,replace
restore
merge 1:1 Date using tmp_file.dta
erase tmp_file.dta
gen Announcement =0
replace Announcement=1 if _merge==3
drop _merge
merge 1:1 Date using "`path_inputs'/01_GlobalFactors_Controls.dta"

// Fill in Missing Dates (with previous observation)
foreach x in "VIX_CLOSE" "SP_CLOSE" "EEM_CLOSE" {
replace `x' = `x'[_n-1] if `x'[_n]==.
}
drop _merge
merge 1:1 Date using "`path_inputs'/01_Arg_ExchangeRate.dta"
drop if _merge==2
drop _merge
merge 1:1 Date using "`path_inputs'/01_Arg_Merval.dta"
drop if _merge==2
drop _merge
save "`path_inputs'/02_Arg_merged.dta",replace


///////////////////////////////////////////////////////////////////////////////////
//DATASET 6: ARGENTINA's BOND: NOMINAL AND IIBS PRICES AND YIELDS
///////////////////////////////////////////////////////////////////////////////////

* Import static information about the bonds
import excel "`path_rawdata'/00_Arg_Bond_Yields.xls", sheet("Info") cellrange(A1:R197) firstrow clear
gen Bond_ID = _n
save "`path_inputs'/01_Arg_Bonds_StaticInfo.dta", replace

*Import bond yields
import excel "`path_rawdata'/00_Arg_Bond_Yields.xls", sheet("Ask_Annual_Yield") cellrange(A2:GO3519) firstrow clear
	local y = 0
	foreach x of varlist _all {
	rename `x' Bond`y'
	local y = `y' +1
	} 
	rename Bond0 date
reshape long Bond, i(date) j(Bond_ID)
sort Bond_ID date
rename Bond Ask_Annual_Yield
save "`path_inputs'/file_Ask_Annual_Yield.dta", replace

*Merge with static informaion of the bond
use "`path_inputs'/01_Arg_Bonds_StaticInfo.dta", clear
keep Curr Maturity MaturityType ISIN BloombergCode CUSIP InflationLinkedNote Bond_ID Cpn CpnFreqDes
merge 1:m Bond_ID  using "`path_inputs'/file_Ask_Annual_Yield.dta"
erase "`path_inputs'/file_Ask_Annual_Yield.dta"
drop _merge

*Sort and save file
sort Bond_ID date
format date %d
format Maturity %d
rename date Date
save "`path_inputs'/01_Arg_Bonds_Data.dta", replace
